package hk.hku.hooku.db;

import hk.hku.hooku.pojo.*;
import hk.hku.hooku.util.GoogleContactsAccess;

import java.sql.*;
import java.util.ArrayList;

public class UserDB {
	public static User getFullUserByGmail(String gmail) {
		User user = null;
		String queryUser = "SELECT id,passwd,name FROM users WHERE users.gmail=?";
		String queryContacts = "SELECT contactName,contactEmail FROM contacts WHERE userid=?";
		String queryProposedEvents = "SELECT url FROM events WHERE proposerId=?";
		DBConnector.connect();
		if (DBConnector.getConn() != null) {
			try {
				PreparedStatement st = DBConnector.getConn().prepareStatement(
						queryUser);
				st.setString(1, gmail);
				ResultSet rs = st.executeQuery();
				if (!rs.next()) {
					return null;
				}
				int id = rs.getInt("id");
				String passwd = rs.getString("passwd");
				String name = rs.getString("name");

				// Get contacts
				st = DBConnector.getConn().prepareStatement(queryContacts);
				st.setInt(1, id);
				rs = st.executeQuery();
				ArrayList<Contact> contacts = new ArrayList<Contact>();
				while (rs.next()) {
					String contactName = rs.getString("contactName");
					String contactEmail = rs.getString("contactEmail");
					Contact contact = new Contact(contactEmail, contactName);
					contacts.add(contact);
				}

				// Get proposed events
				st = DBConnector.getConn()
						.prepareStatement(queryProposedEvents);
				st.setInt(1, id);
				rs = st.executeQuery();
				ArrayList<Event> proposeHistory = new ArrayList<Event>();
				while (rs.next()) {
					String url = rs.getString("url");
					Event event = EventDB.getFullEventByUrl(url);
					proposeHistory.add(event);
				}
				user = new User(gmail, passwd, name, contacts, proposeHistory);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		DBConnector.close();
		return user;
	}

	public static boolean addContacts(String userMail, ArrayList<Contact> contacts) {
		boolean updated = true;
		String querySelect="SELECT id FROM users WHERE gmail=?";
		String queryAdd = "INSERT INTO contacts (userId,contactName,contactEmail) VALUES (?,?,?)";
		DBConnector.connect();
		if (DBConnector.getConn() != null) {
			try {
				PreparedStatement selectSt=DBConnector.getConn().prepareStatement(querySelect);
				selectSt.setString(1, userMail);
				ResultSet rs=selectSt.executeQuery();
				if(!rs.next()){
					return false;
				}
				int userId=rs.getInt("id");
				selectSt.close();
				DBConnector.getConn().setAutoCommit(false);
				for (int i = 0; i < contacts.size(); i++) {
					Contact contact = contacts.get(i);
					PreparedStatement st = DBConnector.getConn()
							.prepareStatement(queryAdd);
					st.setInt(1, userId);
					st.setString(2, contact.getName());
					st.setString(3, contact.getEmail());
					int l = st.executeUpdate();
					if (l == 0) {
						updated = false;
					}
				}
				DBConnector.getConn().commit();
			} catch (SQLException e) {
				e.printStackTrace();
				if (DBConnector.getConn() != null) {
					try {
						DBConnector.getConn().rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
				}
				updated = false;
			}
		}
		DBConnector.close();
		return updated;
	}

	public static boolean addUser(String gmail, String passwd, String name) {
		boolean updated = true;
		String queryAdd = "INSERT INTO users (gmail,passwd,name) VALUES (?,?,?)";
		DBConnector.connect();
		if (DBConnector.getConn() != null) {
			try {
				DBConnector.getConn().setAutoCommit(false);
				PreparedStatement st = DBConnector.getConn().prepareStatement(
						queryAdd);
				st.setString(1, gmail);
				st.setString(2, passwd);
				st.setString(3, name);
				int l = st.executeUpdate();
				if (l == 0) {
					updated = false;
				}
				DBConnector.getConn().commit();
			} catch (SQLException e) {
				e.printStackTrace();
				if (DBConnector.getConn() != null) {
					try {
						DBConnector.getConn().rollback();
					} catch (SQLException e1) {
						e1.printStackTrace();
					}
				}
				updated = false;
			}
		}
		DBConnector.close();
		return updated;
	}
}
